USE Russel
GO

SELECT *
FROM   car INNER JOIN driver ON (owner = name)
SELECT *
FROM   car JOIN driver ON (owner = name)

SELECT *
FROM   car CROSS JOIN driver 
/* result rows =  rows CAR * rows DRIVER ; Attributes  = Attributes CAR + Attributes DRIVER*/

SELECT * FROM CAR,DRIVER 
WHERE CAR.OWNER = DRIVER.NAME;

SELECT        CAR.*, DRIVER.*
FROM            CAR RIGHT OUTER JOIN
                         DRIVER ON CAR.OWNER = DRIVER.NAME
SELECT        CAR.*, DRIVER.*
FROM            CAR LEFT OUTER JOIN
                         DRIVER ON CAR.OWNER = DRIVER.NAME


SELECT owner,count(regno)
FROM car
GROUP BY owner
HAVING count(regno)>1;

SELECT name 
FROM driver
WHERE dob > (SELECT dob FROM driver WHERE name = 'Jim Smith')

SELECT name 
FROM driver
WHERE dob > (SELECT dob FROM driver WHERE name = 'Jim Smith' or name ='Jim Smit')

SELECT name 
FROM driver
WHERE dob = ANY (SELECT dob FROM driver WHERE name = 'Jim Smith' or name ='Jim Smit' or name ='Jan Brown' )

SELECT name 
FROM driver
WHERE dob = ALL (SELECT dob FROM driver WHERE name = 'Jim Smith' or name ='Jim Smit'or name ='Jan Brown' )

SELECT name 
FROM driver
WHERE dob = ALL (SELECT dob FROM driver WHERE name = 'Jim Smith' or name ='Jim Smit' or name ='Jan Brown')


SELECT name 
FROM driver
WHERE dob > (SELECT dob FROM driver WHERE name = 'Jim Smith')


SELECT regno FROM car
WHERE colour = ANY (SELECT colour FROM car WHERE owner = 'Jim Smith')


SELECT colour FROM car WHERE owner = 'Jim Smith'

SELECT regno FROM car
WHERE colour = ANY ('RED','BLUE')

SELECT regno FROM car
WHERE colour IN (SELECT colour FROM car WHERE owner = 'Jim Smith')





SELECT name,dob
FROM driver
WHERE dob > ALL (
              SELECT dob
              FROM car join driver on (owner=name)
              WHERE colour = 'BLUE'
      )
;

